package com.yuyou.fn.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Element;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



/**
 * Created by wens on 2017/7/13.
 */
public class Excels {


    private static String[] ss  = new String[]{ "","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z" } ;

    public static HSSFWorkbook fromTableOfHtml(String html){
        return new Helper(html).build();
    }


    private static class Helper {

        private String html ;

        private HSSFWorkbook workBook = new HSSFWorkbook();
        private HSSFSheet sheet;
        private Map<String, Object> cellsOccupied = new HashMap<>();
        private HSSFCellStyle defaultCellStyle;
        private int maxRow = 0;
        // init
        {
            sheet = workBook.createSheet();
            sheet.setDefaultColumnWidth(20);
            defaultCellStyle = workBook.createCellStyle();
            defaultCellStyle.setWrapText(true);
            defaultCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            defaultCellStyle.setAlignment(HorizontalAlignment.CENTER);
            // border
            short black = new HSSFColor.BLACK().getIndex();
            short thin = CellStyle.BORDER_THIN;
            // top
            defaultCellStyle.setBorderTop(thin);
            defaultCellStyle.setTopBorderColor(black);
            // right
            defaultCellStyle.setBorderRight(thin);
            defaultCellStyle.setRightBorderColor(black);
            // bottom
            defaultCellStyle.setBorderBottom(thin);
            defaultCellStyle.setBottomBorderColor(black);
            // left
            defaultCellStyle.setBorderLeft(thin);
            defaultCellStyle.setLeftBorderColor(black);
            defaultCellStyle.setWrapText(true);

        }

        Helper(String html){
            this.html = html ;
        }

        public HSSFWorkbook build(){
            for (Element table : Jsoup.parseBodyFragment(html).select("table")) {
                processTable(table);
            }
            return workBook ;
        }
        // --
        // private methods

        private void processTable(Element table) {
            int rowIndex = 0;
            if (maxRow > 0) {
                // blank row
                maxRow += 2;
                rowIndex = maxRow;
            }

            for (Element row : table.select("tr")) {
                int colIndex = 0;
                for (Element td : row.select("td, th")) {
                    // skip occupied cell
                    while (cellsOccupied.get(rowIndex + "_" + colIndex) != null) {
                        ++colIndex;
                    }
                    int rowSpan = 0;
                    String strRowSpan = td.attr("rowspan");
                    if (StringUtils.isNotBlank(strRowSpan) &&
                            StringUtils.isNumeric(strRowSpan)) {
                        rowSpan = Integer.parseInt(strRowSpan);
                    }
                    int colSpan = 0;
                    String strColSpan = td.attr("colspan");
                    if (StringUtils.isNotBlank(strColSpan) &&
                            StringUtils.isNumeric(strColSpan)) {
                        colSpan = Integer.parseInt(strColSpan);
                    }
                    // col span & row span
                    if (colSpan > 1 && rowSpan > 1) {
                        spanRowAndCol(td, rowIndex, colIndex, rowSpan, colSpan);
                        colIndex += colSpan;
                    }
                    // col span only
                    else if (colSpan > 1) {
                        spanCol(td, rowIndex, colIndex, colSpan);
                        colIndex += colSpan;
                    }
                    // row span only
                    else if (rowSpan > 1) {
                        spanRow(td, rowIndex, colIndex, rowSpan);
                        ++colIndex;
                    }
                    // no span
                    else {
                        List<String> content = new ArrayList<>(10);
                        if(td.children().size() > 0 ){
                            for(Element c :td.children() ){
                                String text = c.text();
                                if(StringUtils.isNotEmpty(text)){
                                    content.add(text);
                                }
                            }
                        }else{
                            content.add(td.text());
                        }


                        createCell(td, getOrCreateRow(rowIndex), colIndex).setCellValue(StringUtils.join(content,"\r\n"));
                        ++colIndex;
                    }
                }
                ++rowIndex;
            }
        }


        private void spanRow(Element td, int rowIndex, int colIndex, int rowSpan) {
            mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex);
            for (int i = 0; i < rowSpan; ++i) {
                HSSFRow row = getOrCreateRow(rowIndex + i);
                createCell(td, row, colIndex);
                cellsOccupied.put((rowIndex + i) + "_" + colIndex, true);
            }
            getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text());
        }

        private void spanCol(Element td, int rowIndex, int colIndex, int colSpan) {
            mergeRegion(rowIndex, rowIndex, colIndex, colIndex + colSpan - 1);
            HSSFRow row = getOrCreateRow(rowIndex);
            for (int i = 0; i < colSpan; ++i) {
                createCell(td, row, colIndex + i);
            }
            row.getCell(colIndex).setCellValue(td.text());
        }

        private void spanRowAndCol(Element td, int rowIndex, int colIndex,
                                   int rowSpan, int colSpan) {
            mergeRegion(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
            for (int i = 0; i < rowSpan; ++i) {
                HSSFRow row = getOrCreateRow(rowIndex + i);
                for (int j = 0; j < colSpan; ++j) {
                    createCell(td, row, colIndex + j);
                    cellsOccupied.put((rowIndex + i) + "_" + (colIndex + j), true);
                }
            }
            getOrCreateRow(rowIndex).getCell(colIndex).setCellValue(td.text());
        }

        private HSSFCell createCell(Element td, HSSFRow row, int colIndex) {
            HSSFCell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }
            cell.setCellStyle(defaultCellStyle);
            return cell;
        }




        private HSSFRow getOrCreateRow(int rowIndex) {
            HSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
                if (rowIndex > maxRow) {
                    maxRow = rowIndex;
                }
            }
            return row;
        }

        private void mergeRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }
    }


    public static void createDict(XSSFWorkbook workbook, String name , List<String> list ) {
        XSSFSheet dictSheet = workbook.getSheet("数据字典");
        if(dictSheet == null ){
            dictSheet = workbook.createSheet("数据字典");
            workbook.setSheetHidden(workbook.getSheetIndex("数据字典"), Workbook.SHEET_STATE_HIDDEN );
        }
        int physicalNumberOfRows = dictSheet.getPhysicalNumberOfRows();
        int rowIndex = physicalNumberOfRows  ;
        XSSFRow row = dictSheet.createRow(rowIndex);
        List<String> list2  = new ArrayList<>(list);
        list2.add(0,name);
        for(int i = 0 ; i < list2.size() ; i++ ){
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(list2.get(i));
        }

        char start = 'B' ;
        int end = list.size() + 1 ;

        XSSFName xssfName = workbook.createName();
        String formula = String.format("数据字典!$%s$%d:$%s$%d", start, rowIndex + 1, ss[end/26] +  ss[ end%26 ], rowIndex + 1);
        xssfName.setRefersToFormula(formula);
        xssfName.setNameName(name);
    }

    public static XSSFDataValidation addFormulaListConstraint(XSSFSheet sheet ,String name , int firstRow, int endRow, int firstCol, int endCol){
        XSSFDataValidationHelper dataValidationHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint formulaListConstraint = (XSSFDataValidationConstraint)dataValidationHelper.createFormulaListConstraint(name);
        XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(formulaListConstraint,new CellRangeAddressList(firstRow , endRow , firstCol , endCol ));
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
        return validation;
    }

}
